package com.xzwzz.kotlindemo.utils;

import com.xzwzz.kotlindemo.ImportNewVideoData;
import com.xzwzz.kotlindemo.video.ExcelSourceBean;
import com.xzwzz.kotlindemo.video.VideoDataBean;
import com.xzwzz.kotlindemo.video.VideoOriganBean;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

public class ExcelHelper {
    private List<List<String>> allResult = new ArrayList<>();

    //循环文件
    private void loopFile(File file) {
        if (file.isDirectory()) {
            if (file.listFiles() != null) {
                for (File itemFile : file.listFiles()) {
                    loopFile(itemFile);
                }
            }
        } else {
            convertByPath(file.getAbsolutePath());
        }
    }

    //转换
    private void convertByPath(String path) {
        File srcFile = new File(path);
        String fileName = srcFile.getName();
        if (fileName.startsWith(".") || fileName.contains("结果") || fileName.contains("convert")) {
            return;
        }
        List<VideoOriganBean> origanBeans = null;
        try {
            origanBeans = analysisExcel(srcFile, VideoOriganBean.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (origanBeans == null) {
            return;
        }
        List<List<String>> resultList = new ArrayList<>();
        resultList.add(ExcelSourceBean.Companion.getHeaderTitle());
        try {
            List<List<String>> collect = origanBeans.stream().map(new Function<VideoOriganBean, List<String>>() {
                @Override
                public List<String> apply(VideoOriganBean videoOriganBean) {
                    try {
                        return new VideoDataBean(videoOriganBean).toListString();
                    } catch (Exception e) {
//                        e.printStackTrace();
                        System.out.println("失败-->" + "  内容：" + videoOriganBean.toString());
                        return new ArrayList<>();
                    }
                }
            }).collect(Collectors.toList());
            resultList.addAll(collect);
            allResult.addAll(collect);
        } catch (Exception e) {
            e.printStackTrace();
        }
//        ExcelSplitUtil.writeExcel(resultList, "result", srcFile.getParent() + "/convert_" + fileName);
    }

    //转换入口
    public void startConvert(String path) {
        loopFile(new File(path));
    }

    public void startConvertMerge(String path) {
        File srcFile = new File(path);
        allResult.clear();
        allResult.add(ExcelSourceBean.Companion.getHeaderTitle());
        try {
            loopFile(srcFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (srcFile.isDirectory()) {
            ExcelSplitUtil.writeExcel(allResult, "result", srcFile.getParent() + "/convert_" + srcFile.getName() + ".xlsx");
        }
    }

    /**
     * 将excel解析为指定的对象集合 <br>
     * 例如： 要导入的excel格式为 <br>
     */
    public <T> List<T> analysisExcel(File file, Class<T> c) {
        List<T> resultList = new ArrayList<T>();
        InputStream inputStream = null;
        String fileName = null;
        Workbook wb = null;
        try {
            inputStream = new FileInputStream(file);
            fileName = file.getName();
            if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                //如果是2003版本
                if (fileName.endsWith(".xls")) {
                    //1.先解析文件
                    POIFSFileSystem fs = new POIFSFileSystem(inputStream);
                    wb = new HSSFWorkbook(fs);
                } else if (fileName.endsWith(".xlsx")) {//如果是2007以上版本
                    wb = new XSSFWorkbook(inputStream);
                } else {
                    return null;
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        //判断是不是技巧盘
        boolean isJiQiao = fileName.contains("技巧");
        String courseName = "";
//        if (isJiQiao) {
//            courseName = "技巧";
//        } else {
//        }
        List<T> sheetList = new ArrayList<>();
        //循环Sheet，并跳过视频库
        for (int q = 0; q < wb.getNumberOfSheets(); q++) {
            String sheetName = wb.getSheetName(q).trim();
            try {
                if (sheetName.contains("库") || sheetName.contains("标识") || sheetName.equals("卷6")) {
                    continue;
                }
                courseName = ExcelSourceBean.Companion.getCourseName(fileName, sheetName);
                sheetList.clear();
                Sheet sheet = wb.getSheetAt(q);
                //获取第一行（标题行）
                Row titleRow = null;
                int startIndex = 0;
                for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                    if (isTitle(readRow(sheet.getRow(i)))) {
                        titleRow = sheet.getRow(i);
                        startIndex = i;
                        break;
                    }
                }
                //总列数
                int colNum = titleRow.getPhysicalNumberOfCells();
                //总行数
                int rowNum = sheet.getLastRowNum();
                //将标题行一一放入数组
                String[] titles = new String[colNum];

                for (int i = 0; i < colNum; i++) {
                    titles[i] = titleRow.getCell(i).getStringCellValue().replaceAll("\n", "").replaceAll(" ", "").trim();
                }

                titles = filterTitle(titles);

                //获取指定对象所有的字段
                Field fields[] = c.getDeclaredFields();

                Map<String, Field> fieldMap = new HashMap<>();

                for (int i = 0; i < fields.length; i++) {
                    if (fields[i].getAnnotations().length != 0) {
                        fieldMap.put(fields[i].getAnnotation(MappingName.class).name(), fields[i]);
                    }
                }
                DateFormat format = new SimpleDateFormat("h:mm:ss");
                //使用反射机制，将值存入对应对象中
                try {
                    for (int i = startIndex + 1; i < rowNum + 1; i++) {
                        Row currentRow = sheet.getRow(i);
                        if (currentRow == null || !isNotEmpty(readRow(currentRow))) {
                            continue;
                        }
                        T t = c.newInstance();
                        for (int j = 0; j < titles.length; j++) {
                            //当excel中有这个字段
                            if (fieldMap.containsKey(titles[j])) {
                                try {
                                    setValue(t, fieldMap.get(titles[j]), getCellValueByCell(currentRow.getCell(j)), c);
                                } catch (Exception e) {
//                                    System.out.println(e.getMessage() +" => " +isNotEmpty(readRow(currentRow)));
                                }
                            }
                        }
                        if (i != startIndex) {
                            setValue(t, fieldMap.get("排序"), i - startIndex + "", c);
                            setValue(t, fieldMap.get("年份"), ImportNewVideoData.mYear, c);
                            setValue(t, fieldMap.get("科目"), courseName, c);
                            setValue(t, fieldMap.get("版本ID"), ExcelSourceBean.Companion.getCourseId(courseName, sheetName), c);
                            if (isJiQiao) {
                                setValue(t, c.getDeclaredField("realCourse"), ExcelSourceBean.Companion.getCourseName(fileName), c);
                            }
                        }
                        sheetList.add(t);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
                resultList.addAll(sheetList);
                System.out.println(file.getName() + "\t->" + ExcelSourceBean.Companion.getRealVersion(courseName, sheetName) + "\t->" + sheetList.size());
            } catch (Exception e) {
                System.out.println(file.getName() + "\t->" + ExcelSourceBean.Companion.getRealVersion(courseName, sheetName) + "计算失败");
            }
        }
        return resultList;
    }

    private String[] filterTitle(String[] src) {
        String[] titles = src;
        HashSet<String> keys = new HashSet<String>();
        for (int i = 0; i < src.length; i++) {
            if (src[i].contains("二级标题")) {
                src[i] = "专题、考题（二级标题）命名";
            } else if (src[i].contains("三级标题")) {
                src[i] = ("题、视频（三级标题）命名");
            }
            if (keys.contains(src[i])) {
                switch (src[i]) {
                    case "本卷主讲代表":
                        titles[i] = ("主讲代表简介");
                        break;
                    default:
                        titles[i] = ("未知");
                        break;
                }
            } else {
                keys.add(src[i]);
            }
        }
        return titles;
    }

    private <T> void setValue(T t, Field field, String value, Class<T> c) throws Exception {
        //获取类中的字段名
        String fieldName = field.getName();
        //根据字段获取方法名
        String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
        //调用该字段对应的set方法
        Class fieldType = field.getType();

        Method method = c.getMethod(methodName, fieldType);

        method.invoke(t, parseValue(value, fieldType));
    }

    /**
     * 将字符串转化为指定类型的对象
     *
     * @param s----要转化的字符串
     * @param c----目标对象类型
     * @return
     */
    private Object parseValue(String s, Class c) {
        Object obj = null;
        String className = c.getName();
        //excel中的数字解析之后可能末尾会有.0，需要去除
        if (s.endsWith(".0")) s = s.substring(0, s.length() - 2);

        if (className.equals("java.lang.Integer")) { //Integer
            obj = new Integer(s);
        } else if (className.equals("int")) { //int
            obj = (int) Integer.parseInt(s);
        } else if (className.equals("java.lang.String")) { //String
            obj = s;
        } else if (className.equals("java.lang.Double")) { //Double
            obj = new Double(s);
        } else if (className.equals("double")) { //double
            obj = (double) new Double(s);
        } else if (className.equals("java.lang.Float")) { //Float
            obj = new Float(s);
        } else if (className.equals("float")) { //float
            obj = (float) new Float(s);
        } else if (className.equals("java.util.Date")) { //Date
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            try {
                obj = sdf.parse(s);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        } else if (className.equals("long")) { //long
            obj = Long.parseLong(s);
        } else if (className.equals("java.util.Long")) { //Long
            obj = new Long(s);
        }
        return obj;
    }

    private List<String> readRow(Row row) {
        ArrayList<String> list = new ArrayList<>();
        if (row == null) {
            return list;
        }
        for (Cell cell : row) {
            // 根据不同类型转化成字符串
            cell.setCellType(Cell.CELL_TYPE_STRING);
            list.add(cell.getStringCellValue());
        }
        return list;
    }

    private boolean isNotEmpty(List<String> list) {
        list = list.stream().filter(s -> s != null && s.length() > 0).collect(Collectors.toList());
        if (list.size() < 5) {
            return false;
        }
        int count = 0;
        for (String s : list) {
            if (s.trim().isEmpty()) {
                count++;
            }
        }
        return count < 8;
    }

    private boolean isTitle(List<String> list) {
        for (String s : list) {
            for (String t : titleString) {
                if (s.contains(t)) {
                    return true;
                }
            }
        }
        return false;
    }

    private final String[] titleString = new String[]{
            "一级标题", "二级标题"
    };

    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        int cellType = cell.getCellType();
        short dataFormat = cell.getCellStyle().getDataFormat();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (dataFormat == 20 || dataFormat == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (dataFormat == 14 || dataFormat == 31 || dataFormat == 57 || dataFormat == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                } else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                try {
                    SimpleDateFormat sdf = new SimpleDateFormat(cell.getCellStyle().getDataFormatString());
                    double value = Double.parseDouble(cell.getStringCellValue());
                    Date date = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);
                    cellValue = sdf.format(date);
                } catch (Exception e) {
                    cellValue = cell.getStringCellValue();
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                ;
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }

}